﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using System.IO;

namespace Shl01.Models
{
    public class Service
    {
        public string TypeName { get; set; }
        public Dictionary<string,string> dicType { get; set; }
        public string PrimaryName { get; set; }
        public string DBName { get; set; }
        public bool del { get; set; }
        public string GetStr()
        { 
            string paras = "";
            string Column = "";
            string ColumnValue = "";
            string UpdateColumn = "";
            string Params1 = "";//修改
            string Params2 = "";//添加
            string Params3 = "";//删除
            foreach (KeyValuePair<string,string> key in dicType){
                switch (key.Key)
                {
                    case "scsj":
                    case "tjsj":
                    case "xgsj":
                    case "del":
                        continue;
                }
                Params1 = Params1 + string.Format("new SqlParameter('@{0}',SqlNull(g.{0})),\n\t\t\t\t", key.Key);
                switch (key.Value)
                {
                    case "int":
                        paras += string.Format("\t\t\t\t\t\tg.{0} = reader['{0}'] == DBNull.Value ? 0 : Convert.ToInt32(reader['{0}']);\n", key.Key);
                        break;
                    case "string":
                        paras += string.Format("\t\t\t\t\t\tg.{0} = reader['{0}'] == DBNull.Value ? '' : Convert.ToString(reader['{0}']);\n", key.Key);
                        break;
                    case "DateTime":
                        paras += string.Format("\t\t\t\t\t\tg.{0} = reader['{0}'] == DBNull.Value ? new Nullable<DateTime>() : Convert.ToDateTime(reader['{0}']);\n", key.Key);
                        break;
                    case "bool":
                        paras += string.Format("\t\t\t\t\t\tg.{0} = reader['{0}'] == DBNull.Value ? false : Convert.ToBoolean(reader['{0}']);\n", key.Key);
                        break;
                    case "double":
                        paras += string.Format("\t\t\t\t\t\tg.{0} = reader['{0}'] == DBNull.Value ? 0 : Convert.ToDouble(reader['{0}']);\n", key.Key);
                        break;
                    case "float":
                        paras += string.Format("\t\t\t\t\t\tg.{0} = reader['{0}'] == DBNull.Value ? 0 : Convert.ToSingle(reader['{0}']);\n", key.Key);
                        break;
                    default:
                        paras += string.Format("\t\t\t\t\t\tg.{0} = reader['{0}'] == DBNull.Value ? 0 : Convert.ToString(reader['{0}']);\n", key.Key);
                        break;
                }
                if (PrimaryName != key.Key)
                {
                    Params2 = Params2 + string.Format("new SqlParameter('@{0}', SqlNull(g.{0})),\n\t\t\t\t", key.Key);
                    Column += string.Format("{0},", key.Key);
                    ColumnValue += string.Format("@{0},", key.Key);
                    UpdateColumn += string.Format("{0} = @{0},", key.Key);
                }
            }

            if (del)
            {
                UpdateColumn += "xgsj = getdate(),";
            }
            Params3 = Params3 + string.Format("new SqlParameter('@{0}',SqlNull(g.{0})),\n\t\t\t\t", PrimaryName);
            string zhuanHuan = string.Format("{0} g = new {0}(); \n{1}", DBName + ".Models." + TypeName, paras);
            string insertStr = string.Format("insert into {2}({0}) values({1}))",Column.Substring(0,Column.Length-1),ColumnValue.Substring(0,ColumnValue.Length-1),TypeName);

            string UpdateStr = "";
            if (del)
            {
                UpdateStr = string.Format("update {2} set {0} where del = 0 and {1} = @{1}", UpdateColumn.Substring(0, UpdateColumn.Length - 1), PrimaryName, TypeName);
            }
            else
            {
                UpdateStr = string.Format("update {2} set {0} where {1} = @{1}", UpdateColumn.Substring(0, UpdateColumn.Length - 1), PrimaryName, TypeName);
            }
            string str = "";
            if (del)
            {
                str = @"
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using " + DBName + @".Models;
using System.Data.SqlClient;
using System.Data;

namespace " + DBName + @".DAL
{
    [Serializable]
    public static class " + TypeName + @"Service
    {

        /// <summary>
        /// 根据条件返回满足条件的数量
        /// </summary>
        /// <param name='where'>条件</param>
        /// <param name='paras'>sql参数</param>
        /// <returns></returns>
        public static int GetCount(string where = null, SqlParameter[] paras = null)
        {
            string sql = 'select count(*) from " + TypeName + @" where 1=1 ';
            if (where != null)
            {
                sql += where;
            }

            return Convert.ToInt32(SQLHelper.ExecuteScalar(sql, paras));

        }

        /// <summary>
        /// 根据主键返回对象
        /// </summary>
        /// <param name='id'>主键</param>
        /// <returns>对象</returns>
        /// <exception cref='有异常抛出'>有异常抛出</exception>
        public static Grade GetObjectById(string id)
        {
            string sql = 'select * from " + TypeName + @" where del = 0 and " + PrimaryName + @" = @id';
            SqlParameter[] paras =
            {
                new SqlParameter('@id',id)
            };
            try
            {
                SqlDataReader reader = SQLHelper.ExecuteReader(sql, paras);
                if (reader.Read())
                {
                    " + zhuanHuan + @"
                        return g;
                }

            }
            catch
            {

                throw;
            }
            return null;
        }

        /// <summary>
        /// 根据条件查询多个，返回List集合
        /// </summary>
        /// <param name='where'>条件</param>
        /// <param name='paras'>参数</param>
        /// <returns>List集合</returns>
        /// <exception cref='有异常抛出'>有异常抛出</exception>
        public static List<Grade> GetAllList(string where = null,SqlParameter[] paras = null)
        {
            List<Grade> list = new List<Grade>();
            string sql = 'select * from " + TypeName + @" where del = 0 ';
            if (where != null)
            {
                sql += where;
            }
            try
            { 
                SqlDataReader reader = SQLHelper.ExecuteReader(sql,paras);
                while (reader.Read())
                {
                    " + zhuanHuan + @"
                    list.Add(g);
                }
                reader.Close();
            }
            catch
            {

                throw;
            }
            return list;
        }

        /// <summary>
        /// 根据条件查询多个，返回DataSet集合
        /// </summary>
        /// <param name='where'>条件</param>
        /// <param name='paras'>参数</param>
        /// <returns>DataSet集合</returns>
        /// <exception cref='有异常抛出'>有异常抛出</exception>
            public static DataSet GetAllDateSet(string where = null, SqlParameter[] paras = null)
        {
            DataSet ds = null;
            string sql = 'select * from " + TypeName + @" where del = 0 ';
            if (where != null)
            {
                sql += where;
            }
            try
            {
                ds = SQLHelper.ExecuteTable(sql, paras);
            }
            catch
            {

                throw;
            }
            return ds;
        }
        
        /// <summary>
        /// 添加
        /// </summary>
        /// <param name='g'>要添加的对象</param>
        /// <returns>受影响行数</returns>
        /// <exception cref='有异常抛出'>有异常抛出</exception>
        public static int Add(Grade g)
        {
            string sql = @'" + insertStr.Substring(0, insertStr.Length - 1) + @"';
            SqlParameter[] paras =
            {
                " + Params2.Substring(0, Params2.Length - 1) + @"
            };
            try
            {
                return SQLHelper.ExecuteNonQuery(sql,paras);
            }
            catch
            {

                throw;
            }
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name='g'>要修改后的对象</param>
        /// <returns>受影响行数</returns>
        /// <exception cref='有异常抛出'>有异常抛出</exception>
        public static int Update(Grade g)
        {
            string sql = @'" + UpdateStr + @"';
            SqlParameter[] paras =
            {
                " + Params1.Substring(0, Params1.Length - 1) + @"
            };
            try
            {
                return SQLHelper.ExecuteNonQuery(sql,paras);
            }
            catch
            {

                throw;
            }
        }

        /// <summary>
        /// 根据对象删除数据
        /// </summary>
        /// <param name='g'>要删除的对象</param>
        /// <returns>受影响行数</returns>
        /// <exception cref='有异常抛出'>有异常抛出</exception>
        public static int Delete(Grade g)
        {
            string sql = 'update " + TypeName + @" set del = 1,scsj = getdate(),xgsj = getdate() where del = 0 and " + string.Format("{0} = @{0}", PrimaryName) + @"';
            SqlParameter[] paras =
            {
                " + Params3.Substring(0, Params3.Length - 1) + @"
            };
            try
            {
                return SQLHelper.ExecuteNonQuery(sql,paras);
            }
            catch
            {

                throw;
            }
        }

        /// <summary>
        /// 根据主键删除
        /// </summary>
        /// <param name='gradeid'>主键</param>
        /// <returns>受影响行数</returns>
        /// <exception cref='有异常抛出'>有异常抛出</exception>
        public static int Delete(int id)
        {
            string sql = 'update " + TypeName + @" set del = 1,scsj = getdate(),xgsj = getdate() where del = 0 and " + string.Format("{0} = @{0}", PrimaryName) + @"';
            SqlParameter[] paras =
            {
                new SqlParameter(" + string.Format("'@{0}',{1}", PrimaryName, "id") + @")
            };
            try
            {
                return SQLHelper.ExecuteNonQuery(sql,paras);
            }
            catch
            {

                throw;
            }
        }

        /// <summary>
        /// 根据条件删除
        /// </summary>
        /// <param name='where'>条件</param>
        /// <param name='paras'>参数</param>
        /// <returns>受影响行数</returns>
        /// <exception cref='有异常抛出'>有异常抛出</exception>
        public static int Delete(string where = null, SqlParameter[] paras = null)
        {
             string sql = 'delete from " + TypeName + @" where 1 = 1 ';
            if (!string.IsNullOrEmpty(where))
            {
                sql += where;
            }
            try
            {
                return SQLHelper.ExecuteNonQuery(sql, paras);
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name='count'>总页数</param>
        /// <param name='ji'>要访问的页码</param>
        /// <param name='size'>一页显示多少数据</param>
        /// <returns>要显示页的数据</returns>
        /// <exception cref='有异常抛出'>有异常抛出</exception>
        public static List<Grade> GetByFenYe(out int count, int ji = 1, int size = 10)
        {
            string sql = 'select Count(*) from " + TypeName + @" where del = 0 ';
            try
            {
                int zong = Convert.ToInt32(SQLHelper.ExecuteScalar(sql));
                count = zong % size > 0 ? zong / size + 1 : zong / size;
                sql = string.Format('select top {0} * from " + TypeName + @" where del = 0 and " + PrimaryName + @" not in (select top ({0} * {1}) " + PrimaryName + @" from " + TypeName + @" where del = 0 order by " + PrimaryName + @")order by " + PrimaryName + @"', size, ji - 1);
                SqlDataReader reader = SQLHelper.ExecuteReader(sql);
                List<Grade> list = new List<Grade>();
                while (reader.Read())
                {
                    " + zhuanHuan + @"
                    list.Add(g);
                }
                return list;
            }
            catch
            {

                throw;
            }
        }

        /// <summary>
        /// SqlParameter值不可以为空，当值需要为null的时候，需要把值设置为DBNull.Value
        /// </summary>
        /// <param name='obj'>SqlParameter值</param>
        /// <returns></returns>
        public static object SqlNull(object obj)
        {
            if (obj == null)
            {
                return DBNull.Value;
            }

            return obj;
        }
    }
}
";
            }
            else
            {
                str = @"using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using " + DBName + @".Models;
using System.Data.SqlClient;
using System.Data;

namespace " + DBName + @".DAL
{
    [Serializable]
    public static class " + TypeName + @"Service
    {

        /// <summary>
        /// 根据条件返回满足条件的数量
        /// </summary>
        /// <param name='where'>条件</param>
        /// <param name='paras'>sql参数</param>
        /// <returns></returns>
        public static int GetCount(string where = null, SqlParameter[] paras = null)
        {
            string sql = 'select count(*) from " + TypeName + @" where 1=1 ';
            if (where != null)
            {
                sql += where;
            }

            return Convert.ToInt32(SQLHelper.ExecuteScalar(sql, paras));

        }

        /// <summary>
        /// 根据主键返回对象
        /// </summary>
        /// <param name='id'>主键</param>
        /// <returns>对象</returns>
        /// <exception cref='有异常抛出'>有异常抛出</exception>
        public static Grade GetObjectById(string id)
        {
            string sql = 'select * from " + TypeName + @" where " + PrimaryName + @" = @id';
            SqlParameter[] paras =
            {
            new SqlParameter('@id',id)
            };
            try
            {
                SqlDataReader reader = SQLHelper.ExecuteReader(sql, paras);
                if (reader.Read())
                {
                    " + zhuanHuan + @"
                        return g;
                }

            }
            catch
            {

                throw;
            }
            return null;
        }

        /// <summary>
        /// 根据条件查询多个，返回List集合
        /// </summary>
        /// <param name='where'>条件</param>
        /// <param name='paras'>参数</param>
        /// <returns>List集合</returns>
        /// <exception cref='有异常抛出'>有异常抛出</exception>
        public static List<Grade> GetAllList(string where = null,SqlParameter[] paras = null)
        {
            List<Grade> list = new List<Grade>();
            string sql = 'select * from " + TypeName + @" where 1=1 ';
            if (where != null)
            {
                sql += where;
            }
            try
            { 
                SqlDataReader reader = SQLHelper.ExecuteReader(sql,paras);
                while (reader.Read())
                {
                    " + zhuanHuan + @"
                    list.Add(g);
                }
                reader.Close();
            }
            catch
            {

                throw;
            }
            return list;
        }

        /// <summary>
        /// 根据条件查询多个，返回DataSet集合
        /// </summary>
        /// <param name='where'>条件</param>
        /// <param name='paras'>参数</param>
        /// <returns>DataSet集合</returns>
        /// <exception cref='有异常抛出'>有异常抛出</exception>
            public static DataSet GetAllDateSet(string where = null, SqlParameter[] paras = null)
        {
            DataSet ds = null;
            string sql = 'select * from " + TypeName + @" where 1 = 1 ';
            if (where != null)
            {
                sql += where;
            }
            try
            {
                ds = SQLHelper.ExecuteTable(sql, paras);
            }
            catch
            {

                throw;
            }
            return ds;
        }
        
        /// <summary>
        /// 添加
        /// </summary>
        /// <param name='g'>要添加的对象</param>
        /// <returns>受影响行数</returns>
        /// <exception cref='有异常抛出'>有异常抛出</exception>
        public static int Add(Grade g)
        {
            string sql = @'" + insertStr.Substring(0, insertStr.Length - 1) + @"';
            SqlParameter[] paras =
            {
                " + Params2.Substring(0, Params2.Length - 1) + @"
            };
            try
            {
                return SQLHelper.ExecuteNonQuery(sql,paras);
            }
            catch
            {

                throw;
            }
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name='g'>要修改后的对象</param>
        /// <returns>受影响行数</returns>
        /// <exception cref='有异常抛出'>有异常抛出</exception>
        public static int Update(Grade g)
        {
            string sql = @'" + UpdateStr + @"';
            SqlParameter[] paras =
            {
                " + Params1.Substring(0, Params1.Length - 1) + @"
            };
            try
            {
                return SQLHelper.ExecuteNonQuery(sql,paras);
            }
            catch
            {

                throw;
            }
        }

        /// <summary>
        /// 根据对象删除数据
        /// </summary>
        /// <param name='g'>要删除的对象</param>
        /// <returns>受影响行数</returns>
        /// <exception cref='有异常抛出'>有异常抛出</exception>
        public static int Delete(Grade g)
        {
            string sql = 'delete " + TypeName + @" where " + string.Format("{0} = @{0}", PrimaryName) + @"';
            SqlParameter[] paras =
            {
                " + Params3.Substring(0, Params3.Length - 1) + @"
            };
            try
            {
                return SQLHelper.ExecuteNonQuery(sql,paras);
            }
            catch
            {

                throw;
            }
        }

        /// <summary>
        /// 根据id删除
        /// </summary>
        /// <param name='gradeid'>主键</param>
        /// <returns>受影响行数</returns>
        /// <exception cref='有异常抛出'>有异常抛出</exception>
        public static int Delete(int id)
        {
            string sql = 'delete " + TypeName + @" where id = @id';
            SqlParameter[] paras =
            {
                new SqlParameter('@id',id)
            };
            try
            {
                return SQLHelper.ExecuteNonQuery(sql,paras);
            }
            catch
            {

                throw;
            }
        }

        /// <summary>
        /// 根据条件删除
        /// </summary>
        /// <param name='where'>条件</param>
        /// <param name='paras'>参数</param>
        /// <returns>受影响行数</returns>
        /// <exception cref='有异常抛出'>有异常抛出</exception>
        public static int Delete(string where = null, SqlParameter[] paras = null)
        {
            string sql = 'delete from " + TypeName + @" where 1 = 1 ';
            if (!string.IsNullOrEmpty(where))
            {
                sql += where;
            }
            try
            {
                return SQLHelper.ExecuteNonQuery(sql, paras);
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name='count'>总页数</param>
        /// <param name='ji'>要访问的页码</param>
        /// <param name='size'>一页显示多少数据</param>
        /// <returns>要显示页的数据</returns>
        /// <exception cref='有异常抛出'>有异常抛出</exception>
        public static List<Grade> GetByFenYe(out int count, int ji = 1, int size = 10)
        {
            string sql = 'select Count(*) from " + TypeName + @"';
            try
            {
                int zong = Convert.ToInt32(SQLHelper.ExecuteScalar(sql));
                count = zong % size > 0 ? zong / size + 1 : zong / size;
                sql = string.Format('select top {0} * from " + TypeName + @" where " + PrimaryName + @" not in (select top ({0} * {1}) " + PrimaryName + @" from " + TypeName + @" order by " + PrimaryName + @")order by " + PrimaryName + @"', size, ji - 1);
                SqlDataReader reader = SQLHelper.ExecuteReader(sql);
                List<Grade> list = new List<Grade>();
                while (reader.Read())
                {
                    " + zhuanHuan + @"
                    list.Add(g);
                }
                return list;
            }
            catch
            {

                throw;
            }
        }

        /// <summary>
        /// SqlParameter值不可以为空，当值需要为null的时候，需要把值设置为DBNull.Value
        /// </summary>
        /// <param name='obj'>SqlParameter值</param>
        /// <returns></returns>
        public static object SqlNull(object obj)
        {
            if (obj == null)
            {
                return DBNull.Value;
            }

            return obj;
        }
    }
}
";
            }
            
            str = str.Replace("Grade", DBName + ".Models." + TypeName);
            return str.Replace("'","\"");
        }
    }
}
